﻿Option Explicit On
'Option Strict On
Imports System.Data.SqlClient

'*//Performance
'*// http://www.vbforums.com/archive/index.php/t-612740.html
Imports System.Reflection
Imports System.Globalization


Public Class ucMyGrid
    '*// Maakt van deze klasse zelf een datagridview:
    '** This code is dependant on two other classes beeing the clsSelectList and the clsDateTimePicker
    '** If you want to use this piece of code in another project make sure to also copy them
    Inherits Windows.Forms.DataGridView

    Private connectionstring As String = ""
    Public WithEvents ContextMenuStrip1 As New System.Windows.Forms.ContextMenuStrip
    Friend WithEvents ExportToExcelToolStripMenuItem As System.Windows.Forms.ToolStripMenuItem
    Private WithEvents DeleteSelectedRowToolStripMenuItem As System.Windows.Forms.ToolStripMenuItem
    Private WithEvents SaveTableToolStripMenuItem As System.Windows.Forms.ToolStripMenuItem
    Private components As System.ComponentModel.IContainer
    Private arrHiddenColumns As New ArrayList() '** arraylist containing the hidden values

    Private arrOrderByColumns As New ArrayList() '** arraylist containing the order by values
    Private arrTableColumns As New ArrayList() '** contains all columnnames of items that can be saved
    Private arrDateTime As New ArrayList() '** arraylist containing all columns with date - time editor

    Public Structure strucFKParams
        Dim ConnectionString As String
        Dim sSQL As String
        Dim ShowPopup As Boolean
        'Dim hideCol As String
        Dim DefaultRowValue As String
    End Structure

    Public Structure strucUC_FK_Val_FK
        Dim FK_ID As String
        'Dim FK_Name As String
    End Structure

    'Public Structure strucUC_FK_Val_RowID
    '    Dim UCRowID As Dictionary(Of String, strucUC_FK_Val_FK)
    '    'Dim FK As strucUC_FK_Val_FK
    'End Structure

    Public Structure strucUC_FK_Values
        Dim UCRowID As Dictionary(Of String, strucUC_FK_Val_FK)
        'Dim FK As Dictionary(Of String, strucUC_FK_Val_FK)
        Dim Params As strucFKParams
    End Structure

    'Private dicFKValues As New Dictionary(Of String, ArrayList) '** dictionary containing the foreign keys - table contains display value
    'Private dicFKParams As New Dictionary(Of String, strucFKParams) '** dictionary containing foreign keys to be linked toghether with the SQL
    Private dicFKTables As New Dictionary(Of String, Dictionary(Of String, String))
    'Private dicFKPopups As New Dictionary(Of String, Boolean)
    'Private dicFKConnection As New Dictionary(Of String, String) '** dictionary containing foreign keys to be linked toghether connection string

    Private dicUC_FK_Values As New Dictionary(Of String, strucUC_FK_Values)


    Private dicReadOnlyColumns As New Dictionary(Of String, Color) '** arraylist containing the readonly values

    Private sTableName As String = "" '** the string containing the name of the table make sure not null
    Private sCNEdited As String = "Edited" '** The string with the name of the changed column
    Private sUCObjectID As String = "UCRowID" '** The string with the name of the ROW ID column
    Private bLoading As Boolean = True
    Private iNew_UCRowID = 10000

    Private bChanged As Boolean = False '** boolean which contains true if there are unsaved changes
    Private sSql As String = ""
    Private sSaveSql As String = ""
    Private sTableID As String = ""

    Private bUseCustomWidth As Boolean

    Private sDefaultValue As String
    Private bSaveEmptyCells As Boolean

    Public Event RowHidden(ByVal iRowIndex As Integer)

    Private Sub MakeGridDoubleBuffered(ByVal dgv As DataGridView)
        Try
            Dim dgvType As Type = dgv.[GetType]()
            Dim pi As PropertyInfo = dgvType.GetProperty("DoubleBuffered", BindingFlags.Instance Or BindingFlags.NonPublic)
            pi.SetValue(dgv, True, Nothing)
        Catch ex As Exception
            mdlLOGGING.fLOG("ucMygrid => MakeGridDoubleBuffered: " & ex.Message.ToString, 99)
        End Try
    End Sub

    Public WriteOnly Property SaveEmptyCells() As Boolean
        Set(ByVal value As Boolean)
            bSaveEmptyCells = value
        End Set
    End Property

    'Public WriteOnly Property ObjSecurity(ByVal sObject As String) As clsSeccurity
    '    Set(ByVal value As clsSeccurity)
    '        Me.Visible = value.bObjRead(sObject)

    '        If value.bObjEdit(sObject) Then
    '            Me.EditMode = DataGridViewEditMode.EditOnKeystrokeOrF2
    '        Else
    '            Me.EditMode = DataGridViewEditMode.EditProgrammatically
    '        End If

    '        Me.AllowUserToAddRows = value.bObjInsert(sObject)
    '        Me.ReadOnly = Not value.bObjEdit(sObject)
    '        Me.AllowUserToDeleteRows = value.bObjDelete(sObject)
    '        Me.ExportToExcelToolStripMenuItem.Enabled = value.bObjExecute(sObject)
    '        Me.DeleteSelectedRowToolStripMenuItem.Enabled = value.bObjDelete(sObject)

    '        If Me.EditMode = DataGridViewEditMode.EditOnKeystrokeOrF2 _
    '            Or Me.ReadOnly = False Or Me.AllowUserToAddRows _
    '            Or Me.AllowUserToDeleteRows Then
    '            Me.SaveTableToolStripMenuItem.Enabled = True
    '        Else
    '            Me.SaveTableToolStripMenuItem.Enabled = False
    '        End If
    '    End Set
    'End Property

    Public Sub addColumneToHide(ByVal value As String)
        If Not arrHiddenColumns.Contains(value) Then
            arrHiddenColumns.Add(value)
        End If
    End Sub
    Public Sub addColumneAsReadOnly(ByVal value As String, ByVal BackColor As Color)
        If Not dicReadOnlyColumns.ContainsKey(value) Then
            dicReadOnlyColumns.Add(value, BackColor)
        End If
    End Sub
    Public Sub AddColumneOrderBy(ByVal value As String)
        If Not arrOrderByColumns.Contains(value) Then
            arrOrderByColumns.Add(value)
        End If
    End Sub


    Public WriteOnly Property DefaultValue() As String
        Set(ByVal value As String)
            sDefaultValue = value
        End Set
    End Property


    Public WriteOnly Property connection() As String
        Set(ByVal value As String)
            connectionstring = value
        End Set
    End Property

    Public WriteOnly Property tableID() As String
        Set(ByVal value As String)
            sTableID = value
            If Not value = "" Then
                SaveTableToolStripMenuItem.Visible = True
            End If
        End Set
    End Property

    '** Create your SQL so that the first column contains the FK to lookup and the second column the value to be displayed

    Public Sub addForeignKeyLookup(ByVal forKeyName As String, ByVal forKeySQL As String, Optional ByVal sConnectionString As String = "", Optional ByVal bShowPopup As Boolean = True)

        'If Not dicFKParams.ContainsKey(forKeyName) Then
        If Not dicUC_FK_Values.ContainsKey(forKeyName) Then

            dicUC_FK_Values.Add(forKeyName, New strucUC_FK_Values)


            If sConnectionString = "" Then sConnectionString = fGetConnectionString()

            Dim sParam As New strucFKParams
            sParam.sSQL = forKeySQL
            sParam.ShowPopup = bShowPopup
            sParam.ConnectionString = sConnectionString

            Dim FK_Val As New strucUC_FK_Values
            'FK_Val = dicUC_FK_Values(forKeyName)
            FK_Val.Params = sParam
            dicUC_FK_Values(forKeyName) = FK_Val
        End If

    End Sub

    Public Sub addcolumnDefaultValue(ByVal ColName As String, DefaulValue As String)

        If Not DefaulValue = "" Then

            Dim sParam As New strucFKParams

            If Not dicUC_FK_Values.ContainsKey(ColName) Then
                '*// Start with empty params
                dicUC_FK_Values.Add(ColName, New strucUC_FK_Values)

            Else
                '*// Set params
                sParam = dicUC_FK_Values(ColName).Params

            End If

            sParam.DefaultRowValue = DefaulValue

            Dim FK_Val As New strucUC_FK_Values
            FK_Val.Params = sParam
            dicUC_FK_Values(ColName) = FK_Val

        End If

    End Sub


    '** method to add mark a column as being a date and time column -> this causes the date - time picker to be displayed
    Public Sub addDateTimeColumn(ByVal dateColumn As String)
        If Not arrDateTime.Contains(dateColumn) Then
            arrDateTime.Add(dateColumn)
        End If
    End Sub


    '** getters and setters for the tableName
    Public Property TableName() As String
        Get
            Return sTableName
        End Get

        '** When the value is set also update the grid - thus discarding all changes - be carefull with this
        Set(ByVal value As String)



            sTableName = value
            bLoading = True
            SQLstring = "SELECT * FROM [" & value & "]"



        End Set
    End Property

    Public Sub setTableNameWithoutLoading(ByVal name As String)
        sTableName = name
    End Sub

    '** getters and setters for the savestring
    Public WriteOnly Property SaveString() As String
        Set(ByVal value As String)
            sSaveSql = value
        End Set
    End Property

    '** Method that will load all Foreign key tables, it will create a dictionary with the reference
    Private Sub LoadFKItems(ByVal fkFieldName As String, ByVal fkSql As String, ByVal fkconn As String)

        Dim cn As New SQLite.SQLiteConnection   ' Dim cn As New ADODB.Connection()
        Dim da As New SQLite.SQLiteDataAdapter  ' Dim rs As New ADODB.Recordset()
        Dim ds As New DataSet

        Try

            If fkSql <> "" Then

                cn.ConnectionString = fkconn

                If connectionstring <> "" Then
                    cn.Open()

                    ''** configure the recordset
                    'With rs
                    '    .CursorLocation = ADODB.CursorLocationEnum.adUseClient
                    '    .CursorType = ADODB.CursorTypeEnum.adOpenDynamic
                    '    .LockType = ADODB.LockTypeEnum.adLockOptimistic

                    '    .Open(fkSql, cn)
                    'End With
                    da = New SQLite.SQLiteDataAdapter(fkSql, cn)
                    da.Fill(ds, "FK")

                    If ds.Tables(0).Rows.Count > 0 Then
                        'If rs.RecordCount > 0 Then

                        Dim dicFKTemp = New Dictionary(Of String, String)
                        'Do While Not rs.EOF
                        For i = 0 To ds.Tables(0).Rows.Count - 1

                            'If rs.Fields.Count > 1 Then
                            If ds.Tables(0).Columns.Count > 1 Then
                                'dicFKTemp.Add(rs.Fields(0).Value.ToString, rs.Fields(1).Value.ToString)
                                dicFKTemp.Add(ds.Tables(0).Rows(i)(0), ds.Tables(0).Rows(i)(1))
                            Else
                                'dicFKTemp.Add(rs.Fields(0).Value.ToString, rs.Fields(0).Value.ToString)
                                dicFKTemp.Add(ds.Tables(0).Rows(i)(0), ds.Tables(0).Rows(i)(0))
                            End If

                            'rs.MoveNext()
                            'Loop
                        Next i

                        dicFKTables.Add(fkFieldName, dicFKTemp)

                    End If


                End If



                '** clean up
                'rs.Close()
                cn.Close()

            End If

        Catch ex As Exception
            mdlLOGGING.fLOG("ucMygrid => LoadFKItems: " & ex.Message.ToString, 99)
        Finally
            'rs = Nothing
            cn = Nothing
        End Try

    End Sub

    '** get the display name
    Private Function getDisplay(ByVal searchFKID As String, ByVal FKFieldName As String) As String

        '** first check if the dictionary is already filled - if not fill it
        If Not dicFKTables.ContainsKey(FKFieldName) Then
            'LoadFKItems(FKFieldName, dicFKParams.Item(FKFieldName).sSQL, dicFKParams.Item(FKFieldName).ConnectionString)
            With dicUC_FK_Values(FKFieldName).Params
                LoadFKItems(FKFieldName, .sSQL, .ConnectionString)
            End With
        End If

        '** get the dictionary with the id - display link
        If dicFKTables.ContainsKey(FKFieldName) Then
            Dim dicTemp As Dictionary(Of String, String) = dicFKTables.Item(FKFieldName)

            '** check if exist else return original
            If dicTemp.ContainsKey(searchFKID) Then
                Return dicTemp.Item(searchFKID)
            End If
        End If

        Return searchFKID
    End Function

    '** getters and setter of the SQLString - also loads the grid with data when changed
    Public Property SQLstring() As String
        Get
            Return sSql

        End Get
        Set(ByVal value As String)
            Try
                If sTableID = "" Then SaveTableToolStripMenuItem.Visible = False

                mdlLOGGING.fLOG("ucMyGrid => SQLstring: " & value, 101)

                sSql = value
                bLoading = True
                FillGrid(sSql)
                bLoading = False


            Catch ex As Exception
                mdlLOGGING.fLOG("ucMygrid => SQLString: " & ex.Message.ToString, 99)
            End Try
        End Set
    End Property

    '** method to fill the grid
    Private Sub FillGrid(ByVal sSQL As String)
        Dim cn As New SQLite.SQLiteConnection ' Dim cn As New ADODB.Connection()
        Dim da As New SQLite.SQLiteDataAdapter ' Dim rs As New ADODB.Recordset()
        Dim ds As New DataSet
        Dim iColWidth As Integer

        Dim sSQLOrderBy As String

        Dim i, j As Integer  ', jj As Integer

        Try
            bUseCustomWidth = False

            bLoading = True

            Me.SuspendLayout()
            Me.Cursor = Cursors.WaitCursor

            cn.ConnectionString = connectionstring
            If connectionstring <> "" Then
                cn.Open()

                'rs.CursorLocation = ADODB.CursorLocationEnum.adUseClient
                'rs.CursorType = ADODB.CursorTypeEnum.adOpenDynamic
                'rs.LockType = ADODB.LockTypeEnum.adLockOptimistic

                '*// Add ORDER BY Columns to SQL statement
                sSQLOrderBy = " ORDER BY"
                For i = 0 To arrOrderByColumns.Count - 1
                    If i > 0 Then sSQLOrderBy &= ","
                    sSQLOrderBy &= " " & arrOrderByColumns(i)
                Next
                If sSQLOrderBy <> " ORDER BY" Then
                    sSQL &= sSQLOrderBy
                End If

                'rs.Open(sSQL, cn)
                da = New SQLite.SQLiteDataAdapter(sSQL, cn)
                da.Fill(ds, "FillGrid")

                '** get the foreign keys for this table
                'getForeignKeys(cn)

                With Me

                    .Rows.Clear()
                    .Columns.Clear()

                End With

                '** first get the columns

                'For i = 0 To rs.Fields.Count - 1
                For i = 0 To ds.Tables(0).Columns.Count - 1
                    'Dim colName As String = rs.Fields.Item(i).Name
                    Dim colName As String = ds.Tables(0).Columns(i).ColumnName
                    '** add the column to the array wich contains all columns that can be saved
                    arrTableColumns.Add(colName)

                    With Me

                        '** if the type is a boolean the make sure it is a checkboxColumn
                        'If rs.Fields.Item(i).Type.ToString = "adBoolean" Then
                        If ds.Tables(0).Columns(i).DataType = System.Type.GetType(vbBoolean) Then
                            Dim chk As New DataGridViewCheckBoxColumn()
                            .Columns.Add(chk)


                            'ElseIf dicFKParams.ContainsKey(colName) Then
                        ElseIf dicUC_FK_Values.ContainsKey(colName) Then

                            Dim txt As New DataGridViewTextBoxColumn()
                            .Columns.Add(txt)


                            '** if none of the above just ad an ordinary column
                        Else

                            .Columns.Add(colName, "")

                        End If




                        .Columns(i).Name = colName

                        '** get the translation
                        'sName = frmMDIBOK.cTranslations.GetValue(sTableName + "." + colName).sTranslation
                        'iColWidth = frmMDIBOK.cTranslations.GetValue(sTableName + "." + colName).iColWidth

                        If iColWidth <> 0 Then
                            .Columns(i).Width = iColWidth
                            bUseCustomWidth = True
                        End If

                        '.Columns(i).HeaderCell.Value = sName
                        If arrHiddenColumns.Contains(colName) Then
                            .Columns(colName).Visible = False
                        End If


                        '** if it is a foreign key column make it readonly
                        'If dicFKParams.ContainsKey(colName) Or arrDateTime.Contains(colName) Then
                        If dicUC_FK_Values.ContainsKey(colName) Or arrDateTime.Contains(colName) Then
                            .Columns(colName).ReadOnly = True
                        End If

                        If dicReadOnlyColumns.ContainsKey(colName) Then
                            .Columns(colName).ReadOnly = True
                            .Columns(colName).DefaultCellStyle.BackColor = dicReadOnlyColumns(colName)
                        End If

                    End With
                Next i


                With Me

                    '*// om traagheid te voorkomen:
                    If ds.Tables(0).Rows.Count > 150 Then
                        'If rs.RecordCount > 150 Then
                        .AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.None
                    Else
                        If Not bUseCustomWidth Then
                            .AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells
                        Else
                            .AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.None
                        End If
                    End If
                End With

                '** add edited column
                Dim chkCol As New DataGridViewCheckBoxColumn()
                Me.Columns.Add(chkCol)

                Me.Columns(i).HeaderCell.Value = sCNEdited
                Me.Columns(i).Name = sCNEdited
                Me.Columns(i).ReadOnly = True

                '*// Add RowID column - this will allow sorting with FK column
                'chkCol = New DataGridViewcol
                Me.Columns.Add(sUCObjectID, sUCObjectID)
                i += 1
                'Me.Columns(i).HeaderCell.Value = sUCObjectID
                'Me.Columns(i).Name = sUCObjectID
                Me.Columns(i).ReadOnly = True


                If ds.Tables(0).Rows.Count > 0 Then
                    'If rs.RecordCount > 0 Then


                    '** add the amounth of records
                    'Me.Rows.Add(rs.RecordCount)
                    Me.Rows.Add(ds.Tables(0).Rows.Count)
                    'i = 0
                    'Do While Not rs.EOF
                    For i = 0 To ds.Tables(0).Rows.Count - 1
                        For j = 0 To Me.ColumnCount - 3 '** -3 because last column is column which contains boolean if the row is changed

                            '** if a FK is added then 
                            Dim sTempColName As String = Me.Columns.Item(j).Name
                            'If dicFKParams.ContainsKey(sTempColName) Then
                            If dicUC_FK_Values.ContainsKey(sTempColName) Then

                                '** first check if the key is not null
                                'Dim val = rs.Fields(j).Value
                                Dim val = ds.Tables(0)(i).Item(j)

                                If Not (IsDBNull(val) Or val.ToString = "") Then
                                    'Me.Rows(i).Cells(j).Value = getDisplay(rs.Fields(j).Value.ToString, sTempColName)
                                    'Put_In_Arraylist(sTempColName, i, rs.Fields(j).Value.ToString)
                                    Me.Rows(i).Cells(j).Value = getDisplay(ds.Tables(0)(i).Item(j).ToString, sTempColName)
                                    Put_In_Arraylist(sTempColName, i, ds.Tables(0)(i).Item(j).ToString)


                                Else
                                    Me.Rows(i).Cells(j).Value = ""
                                    Put_In_Arraylist(sTempColName, i, "")
                                End If




                            Else

                                'If rs.Fields.Item(j).Type.ToString = "adDBTimeStamp" Then
                                '    If IsDBNull(rs.Fields(j).Value) Then
                                '        Debug.Print("DBNULL")
                                '    End If
                                'End If

                                'Me.Rows(i).Cells(j).Value = rs.Fields(j).Value
                                Me.Rows(i).Cells(j).Value = ds.Tables(0)(i).Item(j).ToString


                            End If


                            '*// Andere opmaak geven aan het item dat al eerder geselecteerd werd...
                            If sDefaultValue <> "" Then
                                'If rs.Fields(j).Value.ToString.ToLower = sDefaultValue.ToLower Then
                                If ds.Tables(0)(i).Item(j).ToString.ToLower = sDefaultValue.ToLower Then
                                    Me.Rows(i).Selected = True
                                    Me.CurrentCell = Me.Rows(i).Cells(0)
                                    Me.Rows(Me.CurrentCell.RowIndex).DefaultCellStyle.BackColor = Color.BlueViolet
                                    Me.Rows(Me.CurrentCell.RowIndex).DefaultCellStyle.ForeColor = Color.WhiteSmoke
                                End If

                            End If

                        Next j



                        '** Add false to the edited column
                        Me.Rows.Item(i).Cells(sCNEdited).Value = False

                        Me.Rows.Item(i).Cells(sUCObjectID).Value = i

                        'i += 1
                        'rs.MoveNext()
                        'Loop
                    Next i

                    With Me
                        .AutoSize = False
                    End With
                End If


                subHideColumns()


                '*// Clean Up
                'rs.Close()
                cn.Close()

            End If

        Catch ex As Exception
            mdlLOGGING.fLOG("ucMygrid => FillGrid: " & ex.Message.ToString, 99)
        Finally

            'rs = Nothing
            cn = Nothing
            bLoading = False

            Me.ResumeLayout()
            Me.Cursor = Cursors.Default

            'iCurrentSec = CLng(sSysTime.wSecond) * 1000 + sSysTime.wMilliseconds

        End Try

    End Sub

    '** method to reload the grid
    Public Sub reload()

        Try

            '** first clear all rows
            Me.Rows.Clear()
            bLoading = True
            FillGrid(sSql)

        Catch ex As Exception
            mdlLOGGING.fLOG("ucMygrid => reload: " & ex.Message.ToString, 99)
        End Try

    End Sub


    Public Sub fillValue_in_Grid(ByVal colName As String, ByVal value As String, ByVal row As Integer, Optional ByVal FKID As String = "")

        Try

            If row >= 0 Then


                Dim RowID As String = Me.Item(Me.Columns("UCRowID").Index, row).Value


                For j = 0 To Me.ColumnCount - 3 '** -3 because last column is column which contains boolean if the row is changed

                    '** if a FK is added then 
                    Dim sTempColName As String = Me.Columns.Item(j).Name

                    If colName = sTempColName Then

                        Me.Rows.Item(row).Cells(j).Value = value

                        '** if it is a FK then also update the array


                        'If dicFKParams.ContainsKey(colName) Then
                        If dicUC_FK_Values.ContainsKey(colName) Then
                            If Not FKID = "-1" And Not FKID = "" Then  '** if FKID = -1 then no item was selected so don't update the dictionary

                                Put_In_Arraylist(colName, RowID, FKID)

                            End If
                        End If

                        Exit For
                    End If

                Next j

            End If

        Catch ex As Exception
            mdlLOGGING.fLOG("ucMygrid => fillValue_in_Grid: " & ex.Message.ToString, 99)
        End Try

    End Sub

    Private Sub subHideColumns()

        Try

            For Each col As DataGridViewColumn In Me.Columns
                Dim sColName As String = col.Name

                '** hide all collums indicated to be hidden and the last "edited" column
                If arrHiddenColumns.Contains(sColName) Or sColName = sCNEdited Or sColName = sUCObjectID Then

                    '================================
                    '==== Indien in debug mode : ====
                    '================================
                    If System.Diagnostics.Debugger.IsAttached = False Then
                        '*// Applicatie gewoon opgestart:
                        col.Visible = False
                    Else '*// Applicatie vanuit VB.NET opgestart (debug)
                        'col.Visible = True
                        col.HeaderText &= " (Debug Mode)"
                    End If
                    '================================


                End If

            Next



        Catch ex As Exception
            mdlLOGGING.fLOG("ucMygrid => subHideColumns: " & ex.Message.ToString, 99)
        End Try

    End Sub


    ''** Public sub that will try to save data in the grid
    'Public Function SaveGrid() As Boolean

    '    Try

    '        Dim dbConnection As New ADODB.Connection()
    '        Dim rsCheckValue As New ADODB.Recordset
    '        dbConnection.ConnectionString = fGetConnectionString()
    '        dbConnection.Open()



    '        For i As Integer = 0 To Me.Rows.Count - 1

    '            '** the SQL statement
    '            Dim sSql As String = ""

    '            '** first check if it is hidden - delete it
    '            If Me.Rows.Item(i).Visible = False Then
    '                sSql = "delete " & _
    '                       "from " & sTableName & " " & _
    '                       "where " & sTableID & "= '" & fGetID(i) & "'"

    '                '** If not hidden change or add the row
    '            Else
    '                '** first check if changed

    '                'If i <> Me.NewRowIndex Then

    '                Dim val = Me.Rows.Item(i).Cells.Item(sCNEdited).Value
    '                If Me.Rows.Item(i).Cells.Item(sCNEdited).Value = True Then

    '                    '** get the id
    '                    Dim id As String = fGetID(i)

    '                    sSql = "SELECT " & sTableID & " " & _
    '                                "FROM " & sTableName & " " & _
    '                                "WHERE " & sTableID & "= '" & fGetID(i) & "'"
    '                    rsCheckValue.Open(sSql, dbConnection, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockOptimistic)
    '                    'If id = "" Then
    '                    If rsCheckValue.EOF Then
    '                        sSql = "INSERT INTO " & sTableName & " " & _
    '                               fGetInsertString(i)
    '                    Else
    '                        sSql = "UPDATE " & sTableName & " " & _
    '                               "SET " & fGetUpdateString(i) & " " & _
    '                               "WHERE " & sTableID & "= '" & fGetID(i) & "'"

    '                    End If

    '                    '*// Clean up
    '                    rsCheckValue.Close()


    '                End If
    '                'End If
    '            End If

    '            '** if the sSql contains a string execute it
    '            If Not sSql = "" Then

    '                mdlLOGGING.fLOG("ucMyGrid => SaveGrid: " & sSql, 102)

    '                dbConnection.Execute(sSql)

    '            End If

    '            '** if the save is succesfull then remove the edited -> necessary if only one table is saved
    '            bLoading = True
    '            Me.Rows.Item(i).Cells.Item(sCNEdited).Value = False
    '            bLoading = False


    '        Next

    '        '** clean up
    '        dbConnection.Close()
    '        rsCheckValue = Nothing
    '        dbConnection = Nothing

    '        '** reset the changed boolean
    '        bChanged = False

    '        Return True


    '    Catch ex As Exception
    '        mdlLOGGING.fLOG("ucMygrid => SQLString: " & ex.Message.ToString, 99)
    '        Return False
    '    End Try

    'End Function

    '** Private function that constructs the insert string
    Private Function fGetInsertString(ByVal row As Integer) As String

        Try

            Dim sCols As String = ""
            Dim sValues As String = ""
            Dim dVal As Date


            For i As Integer = 0 To Me.Columns.Count - 3 '** -3 because last column is column which contains boolean if the row is changed

                Dim sColName As String = Me.Columns.Item(i).Name

                '** Only columns that exist in the table can be saved - this is for additional columns added as helpers (like button columns)
                If arrTableColumns.Contains(sColName) Then

                    '** ID cannot change
                    If Not sColName = sTableID Then
                        Dim sCellValue As String = ""

                        '** if it is a foreign key column then get the value from the dictionary
                        'If dicFKValues.ContainsKey(sColName) Then
                        If dicUC_FK_Values.ContainsKey(sColName) Then

                            'Dim arrTmp = dicFKValues.Item(sColName)

                            Dim val = Me.Rows.Item(row).Cells(i).Value

                            If Not val = "" Then
                                'sCellValue = arrTmp.Item(Me.Item(Me.Columns(sUCObjectID).Index, row).Value)
                                sCellValue = dicUC_FK_Values(sColName).UCRowID(Me.Item(Me.Columns(sUCObjectID).Index, row).Value.ToString).FK_ID

                            End If


                            '** For date time reformat or else an error will be thrown
                        ElseIf arrDateTime.Contains(sColName) Then

                            dVal = Me.Rows.Item(row).Cells(i).Value
                            If Not dVal = "#12:00:00 AM#" Then
                                'sCellValue = getLongDateSQLFormat(val)
                                sCellValue = dVal.ToString("yyyy-MM-dd HH:m:ss")
                            Else
                                sCellValue = ""
                            End If


                        Else
                            Dim val = Me.Rows.Item(row).Cells(i).Value
                            If Not val = Nothing Then

                                sCellValue = Me.Rows.Item(row).Cells(i).Value.ToString
                            End If

                        End If

                        If Not sCellValue = "" Or bSaveEmptyCells Then
                            sCols = sCols & ", " & sColName
                            sValues = sValues & ", '" & sCellValue.Replace("'", "''") & "'"
                        End If

                    End If
                End If

            Next i
            If sCols <> "" Then sCols = sCols.Substring(2)
            If sValues <> "" Then sValues = sValues.Substring(2)
            Return "(" & sCols & ") VALUES (" & sValues & ")"


        Catch ex As Exception
            mdlLOGGING.fLOG("ucMygrid => fGetInsertString: " & ex.Message.ToString, 99)
            Return ""
        End Try

    End Function

    '** Private function that constructs the update string
    Private Function fGetUpdateString(ByVal row As Integer) As String

        Try

            Dim sReturn As String = ""
            Dim dVal As Date

            For i As Integer = 0 To Me.Columns.Count - 3 '** -2 because last column is column which contains boolean if the row is changed

                Dim sColName As String = Me.Columns.Item(i).Name

                '** Only columns that exist in the table can be saved - this is for additional columns added as helpers (like button columns)
                If arrTableColumns.Contains(sColName) Then

                    '** ID cannot change
                    If Not sColName = sTableID Then
                        Dim sCellValue As String = ""

                        '** for foreign key lookup the key
                        'If dicFKValues.ContainsKey(sColName) Then
                        If dicUC_FK_Values.ContainsKey(sColName) Then

                            'Dim arrTmp = dicFKValues.Item(sColName)

                            'sCellValue = arrTmp.Item(Me.Item(Me.Columns(sUCObjectID).Index, row).Value)
                            sCellValue = dicUC_FK_Values(sColName).UCRowID(Me.Item(Me.Columns(sUCObjectID).Index, row).Value).FK_ID

                            '    '** For date time reformat or else an error will be thrown
                        ElseIf arrDateTime.Contains(sColName) Then

                            '    Dim val = Me.Rows.Item(row).Cells(i).Value.ToString
                            '    If Not val = "" Then
                            '        sCellValue = getLongDateSQLFormat(val)
                            '    End If

                            dVal = Me.Rows.Item(row).Cells(i).Value
                            If Not IsDBNull(dVal) Then
                                'sCellValue = getLongDateSQLFormat(val)
                                sCellValue = dVal.ToString("yyyy-MM-dd HH:m:ss")

                            End If

                        ElseIf Not Me.Rows.Item(row).Cells(i).Value Is Nothing Then
                            sCellValue = Me.Rows.Item(row).Cells(i).Value.ToString
                        Else
                            sCellValue = ""

                        End If


                        If Not sCellValue = "" Or bSaveEmptyCells Then
                            sReturn = sReturn & ", " & sColName & " = '" & sCellValue.Replace("'", "''") & "'"
                        End If



                    End If
                End If
            Next i
            sReturn = sReturn.Substring(2)
            Return sReturn

        Catch ex As Exception
            mdlLOGGING.fLOG("ucMygrid => fGetUpdateString: " & ex.Message.ToString, 99)
            Return ""
        End Try

    End Function

    '** Function to search for the ID
    '** returns the integer if found, 0 if new row and -1 if something is wrong
    Private Function fGetID(ByVal row As Integer) As String
        Dim i As Integer = 0

        '** iterate over the columns
        For Each col As DataGridViewColumn In Me.Columns
            '** when colum name of ID is encounterd then return the ID
            If col.Name.ToLower = sTableID.ToLower Then
                If Not Me.Rows.Item(row).Cells(i).Value = Nothing Then
                    Dim val = Me.Rows.Item(row).Cells(i).Value.ToString
                    Return val
                Else
                    Return ""
                End If

            End If
            i = i + 1
        Next

        Return -1

    End Function


    Public Sub New() 'als de klasse de eerste keer wordt aangemaakt zetten we de layout en de rechten al goed.

        MakeGridDoubleBuffered(Me)
        bSaveEmptyCells = False

        sDefaultValue = ""

        With Me
            .Name = "ucMygrid"
            .GridColor = Color.Black
            .RowHeadersVisible = True
            .ColumnHeadersHeight = 50
            .SelectionMode = DataGridViewSelectionMode.RowHeaderSelect
            .MultiSelect = False
            .Dock = DockStyle.Fill
            .EnableHeadersVisualStyles = False
            .DefaultCellStyle.SelectionBackColor = Color.Blue
            .Visible = True
            .ScrollBars = ScrollBars.Both

            '*// Rechten ==> DEFAULT geen EDIT of INSERT
            .EditMode = DataGridViewEditMode.EditProgrammatically
            .AllowUserToAddRows = False
            '*// Rechten ==> DEFAULT niet zichtbaar
            .Visible = False

        End With

        InitializeComponent()


        '*// Security
        Me.EditMode = DataGridViewEditMode.EditProgrammatically
        Me.AllowUserToAddRows = False
        Me.AllowUserToDeleteRows = False
        Me.ExportToExcelToolStripMenuItem.Enabled = True
        Me.DeleteSelectedRowToolStripMenuItem.Enabled = False
        Me.SaveTableToolStripMenuItem.Enabled = False


    End Sub

    Private Sub ucMyGrid_AllowUserToDeleteRowsChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.AllowUserToDeleteRowsChanged
        Me.DeleteSelectedRowToolStripMenuItem.Visible = Me.AllowUserToDeleteRows
    End Sub

    Private Sub ucMyGrid_CellDoubleClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles Me.CellDoubleClick

        Try

            Me.sPopup(e.RowIndex, e.ColumnIndex)

        Catch ex As Exception
            mdlLOGGING.fLOG("ucMygrid => ucMyGrid_CellDoubleClick: " & ex.Message.ToString, 99)
        End Try

    End Sub




    Private Sub ucMyGrid_CellValueChanged(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles Me.CellValueChanged

        Try


            '** change the last column (= edit column) to true if a value is changed
            If Not bLoading Then

                If Not Me.Columns(sCNEdited) Is Nothing Then
                    If e.ColumnIndex <> Me.Columns(sCNEdited).Index And e.RowIndex > -1 Then
                        If Not Me.SelectedCells.Count = 0 Then 'And Me.NewRowIndex <> Me.CurrentRow.Index Then

                            Me.Item(Me.Columns(sCNEdited).Index, e.RowIndex).Value = True
                            If Me.Item(Me.Columns(sUCObjectID).Index, e.RowIndex).Value Is Nothing Then
                                Me.Item(Me.Columns(sUCObjectID).Index, e.RowIndex).Value = iNew_UCRowID + e.RowIndex
                            End If

                            bChanged = True

                        End If
                    End If

                End If


                'If dicUC_FK_Values(Me.Columns(e.ColumnIndex).Name).Params.DefaultRowValue <> "" Then

                '    Dim sDefaultValue As String = dicUC_FK_Values(Me.Columns(e.ColumnIndex).Name).Params.DefaultRowValue




                'End If


            End If


        Catch ex As Exception
            mdlLOGGING.fLOG("ucMygrid => ucMyGrid_CellValueChanged: " & ex.Message.ToString, 99)
        End Try

    End Sub

    Private Sub me_MouseClick(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles Me.MouseClick
        Try
            '*// Bij rechtermuisklik laten we een menustripje zien waarin we kunnen kiezen om o.a. een export naar excel te doen.
            If e.Button = Windows.Forms.MouseButtons.Right Then
                ContextMenuStrip1.Show(sender, e.Location)
            End If

        Catch ex As Exception
            mdlLOGGING.fLOG("ucMygrid => me_MouseClick: " & ex.Message.ToString, 99)
        End Try
    End Sub

    Public Sub Put_In_Arraylist(ByVal FieldName As String, ByVal index_UCRowID As String, ByVal value As String)

        Dim bAdd As Boolean = False '*// Add or update 'UCRowID' item

        Try

            '** if the dictionary does not contain the field, add it
            If Not dicUC_FK_Values.ContainsKey(FieldName) Then
                dicUC_FK_Values.Add(FieldName, New strucUC_FK_Values)
            End If

            '*// Index can't be nothing
            If Not index_UCRowID Is Nothing Then

                If dicUC_FK_Values(FieldName).UCRowID Is Nothing Then
                    bAdd = True
                ElseIf Not dicUC_FK_Values(FieldName).UCRowID.ContainsKey(index_UCRowID) Then
                    bAdd = True
                End If


                If bAdd Then
                    '*// Add 'UCRowID'
                    Dim TmpDic As New strucUC_FK_Values
                    Dim TmdDicFK As New strucUC_FK_Val_FK
                    Dim TmpDicDic As New Dictionary(Of String, strucUC_FK_Val_FK)

                    If Not dicUC_FK_Values(FieldName).UCRowID Is Nothing Then
                        '*// Get saved values
                        TmpDic = dicUC_FK_Values(FieldName)
                        TmpDicDic = TmpDic.UCRowID
                    Else
                        '*// Get saved params
                        TmpDic.Params = dicUC_FK_Values(FieldName).Params
                    End If

                    TmdDicFK.FK_ID = value
                    TmpDicDic.Add(index_UCRowID, TmdDicFK)

                    TmpDic.UCRowID = TmpDicDic
                    dicUC_FK_Values(FieldName) = TmpDic

                Else
                    '*// Update 'UCRowID'
                    Dim TmdDicFK As New strucUC_FK_Val_FK

                    TmdDicFK.FK_ID = value

                    dicUC_FK_Values(FieldName).UCRowID(index_UCRowID) = TmdDicFK

                End If
            Else
                'TODO: Errorlogging

            End If

        Catch ex As Exception
            mdlLOGGING.fLOG("ucMygrid => Put_In_Arraylist: " & ex.Message.ToString, 99)
        End Try

    End Sub

    '** Method that will check if the givven column is a foreign key
    Public Function fIsMarkedAsForeignKey(ByVal colName As String) As Boolean

        If dicUC_FK_Values.ContainsKey(colName) Then Return True

        Return False

    End Function

    '** Sub that is called to check if the cell needs a popup for input
    Public Sub sPopup(ByVal row As Integer, ByVal col As Integer, Optional ByVal sKeyCode As String = "")

        Try
            If col >= 0 And row >= 0 Then
                Dim colName As String = Me.Columns.Item(col).Name

                If Not Me.ReadOnly Then

                    '** display popup datetime only for columns marked as date time
                    If arrDateTime.Contains(colName) Then
                        Dim dtPicker As New clsDateTimePicker
                        Dim ret As DateTime
                        Dim init As String = ""
                        If Not IsDBNull(Me.SelectedCells.Item(0).Value) Then
                            init = Me.SelectedCells.Item(0).Value
                        End If


                        ret = dtPicker.return_value(init)

                        If row = Me.NewRowIndex Then
                            Me.Rows.Add()
                            Me.Rows(Me.NewRowIndex).Selected = False
                            Me.Rows(row).Selected = True
                        End If

                        If Not ret = New DateTime Then
                            '** KR : convert the date to mm dd yyyy else errors occur
                            Me.Rows.Item(row).Cells(col).Value = getLongDateSQLFormat(ret)
                        End If

                    End If

                    '** display popup only for foreign keys
                    If fIsMarkedAsForeignKey(colName) Then
                        Dim sel As New clsSelectList
                        Dim ret As ArrayList

                        Dim sSQL As String
                        Dim bShowPopup As Boolean
                        Dim cnPopup As String

                        With dicUC_FK_Values(colName).Params
                            sSQL = .sSQL
                            bShowPopup = .ShowPopup
                            cnPopup = .ConnectionString
                        End With

                        If bShowPopup Then '** if the sSQL string is empty don't show a popup
                            ret = sel.Initialize_Feedback_fullRecord(cnPopup, sSQL, fGetFKValue(colName, Me.Item(Me.Columns(sUCObjectID).Index, Me.CurrentRow.Index).Value), sKeyCode)

                            If Not ret.Count = 0 Then


                                If row = Me.NewRowIndex Then
                                    Me.Rows.Insert(Me.NewRowIndex, 1)
                                    Me.Rows(Me.NewRowIndex).Selected = False
                                    Me.Rows(row).Selected = True

                                End If

                                '** put the first item returned in the grid
                                If ret.Count = 1 Then
                                    Me.Rows.Item(row).Cells(col).Value = ret.Item(0)
                                Else
                                    Me.Rows.Item(row).Cells(col).Value = ret.Item(1)
                                End If


                                '** and the second in an arraylist
                                Put_In_Arraylist(colName, Me.Item(Me.Columns(sUCObjectID).Index, row).Value, ret.Item(0))
                                Me.RefreshEdit()
                            End If
                        End If
                    End If
                    If row >= 0 Then
                        Me.Rows.Item(row).Cells(col).Selected = True
                    End If
                Else
                    Beep()

                End If
            End If


        Catch ex As Exception
            mdlLOGGING.fLOG("ucMygrid => sPopup: " & ex.Message.ToString, 99)
        End Try

    End Sub
    Public Function fGetFKValue(ByVal fieldName As String, ByVal index As String) As String

        Try

            If dicUC_FK_Values.ContainsKey(fieldName) Then

                If Not index = Nothing And Not dicUC_FK_Values(fieldName).UCRowID Is Nothing Then

                    If dicUC_FK_Values(fieldName).UCRowID.ContainsKey(index) Then

                        Return dicUC_FK_Values(fieldName).UCRowID(index).FK_ID

                    Else
                        'TODO: Error logging
                    End If
                End If
            End If

        Catch ex As Exception
            mdlLOGGING.fLOG("ucMygrid => fGetFKValue: " & ex.Message.ToString, 99)
        End Try

        Return ""

    End Function


    Private Sub datagridviewvariable_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles Me.KeyDown

        Try
            '*// Op delete drukken = hudige rij tijdelijk verwijderen
            If e.KeyCode = Keys.Delete Then



                If Me.SelectedRows.Count > 0 Then
                    HideRow(CurrentCell.RowIndex)
                Else

                    If Me.ReadOnly = False Then
                        'If Column name is FK >> OR << column is NIET alleen lezen
                        'If dicFKValues.ContainsKey(Me.Columns(CurrentCell.ColumnIndex).Name) _
                        '        Or Not Me.Columns(Me.CurrentCell.ColumnIndex).ReadOnly Then
                        If dicUC_FK_Values.ContainsKey(Me.Columns(CurrentCell.ColumnIndex).Name) _
                          Or Not Me.Columns(Me.CurrentCell.ColumnIndex).ReadOnly Then


                            Me.Item(Me.CurrentCell.ColumnIndex, CurrentCell.RowIndex).Value = ""
                            Put_In_Arraylist(Me.Columns(CurrentCell.ColumnIndex).Name, CurrentCell.RowIndex, "")



                        Else
                            Beep()
                        End If
                    Else
                        Beep()
                    End If

                End If


                e.Handled = True

            ElseIf e.KeyCode = Keys.Enter Then

                If Not Me.ReadOnly And Me.RowCount > 0 Then
                    sPopup(Me.CurrentCell.RowIndex, Me.CurrentCell.ColumnIndex)
                    e.Handled = True
                End If

            ElseIf e.KeyCode >= Keys.A And e.KeyCode <= Keys.Z Then
                If Not Me.CurrentCell Is Nothing Then
                    If dicUC_FK_Values.ContainsKey(Me.Columns(Me.CurrentCell.ColumnIndex).Name) Then
                        sPopup(Me.CurrentCell.RowIndex, Me.CurrentCell.ColumnIndex, Convert.ToChar(e.KeyCode))
                        e.Handled = True
                    End If
                End If
            End If

        Catch ex As Exception
            mdlLOGGING.fLOG("ucMygrid => Keydown " & ex.Message.ToString, 1)
        End Try
    End Sub

    Private Sub HideRow(ByVal iRowIndex As Integer)
        Try
            Dim iColIndex As Integer

            If Me.AllowUserToDeleteRows Then

                '*// Empty all fields of this row
                For iColIndex = 0 To Me.ColumnCount - 1
                    If Me.Columns(iColIndex).Visible Then
                        If Not Me.Columns(iColIndex).ReadOnly Then
                            Me.Item(iColIndex, iRowIndex).Value = ""
                        End If

                    End If
                Next

                If iRowIndex <> Me.NewRowIndex Then
                    Me.Rows(iRowIndex).Visible = False
                End If

            Else
                Beep()
            End If

            RaiseEvent RowHidden(iRowIndex)

        Catch ex As Exception
            mdlLOGGING.fLOG("ucMygrid => HideRow: " & ex.Message.ToString, 99)
        End Try
    End Sub

    Protected Overrides Sub Finalize()
        MyBase.Finalize()
    End Sub

    Private Sub InitializeComponent()
        Me.components = New System.ComponentModel.Container
        Me.ContextMenuStrip1 = New System.Windows.Forms.ContextMenuStrip(Me.components)
        Me.DeleteSelectedRowToolStripMenuItem = New System.Windows.Forms.ToolStripMenuItem
        Me.SaveTableToolStripMenuItem = New System.Windows.Forms.ToolStripMenuItem
        Me.ExportToExcelToolStripMenuItem = New System.Windows.Forms.ToolStripMenuItem
        Me.ContextMenuStrip1.SuspendLayout()
        CType(Me, System.ComponentModel.ISupportInitialize).BeginInit()
        Me.SuspendLayout()
        '
        'ContextMenuStrip1
        '
        Me.ContextMenuStrip1.Items.AddRange(New System.Windows.Forms.ToolStripItem() {Me.DeleteSelectedRowToolStripMenuItem, Me.SaveTableToolStripMenuItem, Me.ExportToExcelToolStripMenuItem})
        Me.ContextMenuStrip1.Name = "ContextMenuStrip1"
        Me.ContextMenuStrip1.RenderMode = System.Windows.Forms.ToolStripRenderMode.Professional
        Me.ContextMenuStrip1.Size = New System.Drawing.Size(181, 70)
        '
        'DeleteSelectedRowToolStripMenuItem
        '
        Me.DeleteSelectedRowToolStripMenuItem.Name = "DeleteSelectedRowToolStripMenuItem"
        Me.DeleteSelectedRowToolStripMenuItem.Size = New System.Drawing.Size(180, 22)
        Me.DeleteSelectedRowToolStripMenuItem.Text = "Delete selected row"
        '
        'SaveTableToolStripMenuItem
        '
        Me.SaveTableToolStripMenuItem.Name = "SaveTableToolStripMenuItem"
        Me.SaveTableToolStripMenuItem.Size = New System.Drawing.Size(180, 22)
        Me.SaveTableToolStripMenuItem.Text = "Save this Table"
        '
        'ExportToExcelToolStripMenuItem
        '
        Me.ExportToExcelToolStripMenuItem.Name = "ExportToExcelToolStripMenuItem"
        Me.ExportToExcelToolStripMenuItem.Size = New System.Drawing.Size(180, 22)
        Me.ExportToExcelToolStripMenuItem.Text = "Export to MS Excel"
        '
        'ucMyGrid
        '
        Me.ContextMenuStrip1.ResumeLayout(False)
        CType(Me, System.ComponentModel.ISupportInitialize).EndInit()
        Me.ResumeLayout(False)

    End Sub

    '===============================================================================
    '== The handlers for the popup items
    '===============================================================================

    Private Sub SaveTableToolStripMenuItem_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles SaveTableToolStripMenuItem.Click
        Try
            'SaveGrid()

            '** reset the changed boolean
            'bChanged = False



        Catch ex As Exception
            mdlLOGGING.fLOG("ucMygrid => SaveTableToolStripMenuItem_Click: " & ex.Message.ToString, 99)
        End Try
    End Sub

    Private Sub DeleteSelectedRowToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DeleteSelectedRowToolStripMenuItem.Click
        Try

            If Not Me.SelectedRows.Count = 0 Then

                HideRow(Me.SelectedRows(0).Index)
                'Me.Rows(Me.SelectedRows(0).Index).Visible = False

            End If

            If Not Me.SelectedCells.Count = 0 Then
                HideRow(Me.SelectedCells.Item(0).OwningRow.Index)
                'Me.SelectedCells.Item(0).OwningRow.Visible = False
            End If



        Catch ex As Exception
            mdlLOGGING.fLOG("ucMygrid => DeleteSelectedRowToolStripMenuItem_Click: " & ex.Message.ToString, 99)
        End Try



    End Sub

    Public Function isModelChanged() As Boolean
        Return bChanged
    End Function

    '** 
    Public Sub setModelChanged(ByVal bNewChanged As Boolean)
        bChanged = bNewChanged
    End Sub

    Public Function getLongDateSQLFormat(ByVal data As Date) As String
        'getLongDateSQLFormat = ""
        Try
            'getLongDateSQLFormat = Format(data, "yyyy-MM-dd HH:m:ss")
            Return data.ToString("g", CultureInfo.CurrentCulture)
            'Return data.ToString("yyyy-MM-dd HH:m:ss")


        Catch e As Exception
            MessageBox.Show(e.ToString, "getLongDateSQLFormat Exception", MessageBoxButtons.OK)
            Err.Clear()

        End Try

        Return ""

    End Function


    Private Sub ExportToExcelToolStripMenuItem_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles ExportToExcelToolStripMenuItem.Click

        GridExportToExcel(Me)

    End Sub

    Private Sub ucMyGrid_SortCompare(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewSortCompareEventArgs) Handles Me.SortCompare

        '*// http://stackoverflow.com/questions/870752/datagridview-sorting-with-nulls-in-datetime-column

        Try

            If e.CellValue1 Is Nothing OrElse e.CellValue1.Equals(DBNull.Value) Then
                If e.CellValue2 Is Nothing OrElse e.CellValue2.Equals(DBNull.Value) Then
                    e.SortResult = 0
                Else
                    e.SortResult = 1
                End If
            Else
                If e.CellValue2 Is Nothing OrElse e.CellValue2.Equals(DBNull.Value) Then
                    e.SortResult = -1
                Else
                    e.SortResult = DirectCast(e.CellValue1, IComparable).CompareTo(DirectCast(e.CellValue2, IComparable))
                End If
            End If
            e.Handled = True

        Catch ex As Exception
            mdlLOGGING.fLOG("ucMygrid => ucMyGrid_SortCompare: " & ex.Message.ToString, 99)
        End Try

    End Sub

    Private Sub ucMyGrid_RowEnter(sender As Object, e As System.Windows.Forms.DataGridViewCellEventArgs) Handles Me.RowEnter


        If Me.NewRowIndex = e.RowIndex Then

            Dim i As Integer
            For i = 0 To Me.ColumnCount - 1

                If dicUC_FK_Values.ContainsKey(Me.Columns(i).Name) Then
                    If dicUC_FK_Values(Me.Columns(i).Name).Params.DefaultRowValue <> "" Then

                        Me.Item(i, e.RowIndex).Value = dicUC_FK_Values(Me.Columns(i).Name).Params.DefaultRowValue

                    End If
                End If
            Next i

        End If


    End Sub
End Class
